Psql in varchar2, --产生dataset的sql语句
PROCEDURE DotNetPagination(
Private Sub gridbind(ByVal pindex As Integer, ByVal psql As String, Optional ByVal psize As Integer = 10)
)
v_sql varchar2(1000);
Pindex in number,
Dim cmd As New OracleClient.OracleCommand()
execute immediate v_sql into v_count;
End If
conn.ConnectionString = "Password=gzdlgis;User ID=gzdlgis;Data Source=gzgis"
cmd.Parameters.Add("psql", Data.OracleClient.OracleType.VarChar).Value = psql '"select rownum rn,t.* from cd_ssxl t"
以下是在.net中调用Oracle分页存储过程的步骤。

cmd.Parameters.Add("psqlcount", OracleType.VarChar).Value = psql
);
--***************************************************************************************
--Psql := 'select rownum rn,t.* from cd_ssxl t' ; --要求必须包含rownum字段
---------------------------------------------------------------------------------------
'------------------------------------------------------------------------------------
Psqlcount in varchar2,
Dim dr As OracleClient.OracleDataReader
-- Created : 2004-12-13 13:30:30
Pcount out number,
-- Author : good_hy
cmd.CommandText = "DotNot.DotNetPagination"
v_cur out type_cur
--**************************************************************************************
End Sub
cmd.Parameters.Add("pindex", Data.OracleClient.OracleType.Number).Value = pindex
v_sql := 'select count(*) from (' || Psqlcount || ')';
Pcount := ceil(v_count/Psize);
dr = cmd.ExecuteReader()
'------------------------------------------------------------------------------------
Dim conn As New OracleClient.OracleConnection()
Begin
-- Purpose :
Me.DataGrid1.DataSource = dr
execute immediate v_sql into v_prcount;
v_Phei number;
End Sub
Me.DataGrid1.AllowCustomPaging = True

open v_cur for v_sql;
Response.Write("总计页数 " & cmd.Parameters("pcount").Value)
cmd.CommandType = CommandType.StoredProcedure
由于设想在.net中把复杂的sql语句生成，所以在存储过程中没有去考虑生成sql语句的问题。
Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
v_sql VARCHAR2(1000);
Pindex in number, --分页索引
cmd.CommandText = "DotNot.DotNetPageRecordsCount"
cmd.Parameters.Add("psize", Data.OracleClient.OracleType.Number).Value = psize
procedure DotNetPageRecordsCount(
procedure DotNetPageRecordsCount(
create or replace package DotNet is
cmd.Parameters.Add("pcount", Data.OracleClient.OracleType.Number).Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Psize in number,
cmd.Parameters.Add("prcount", OracleType.Number).Direction = ParameterDirection.Output
--**************************************************************************************
Psql in varchar2,
end DotNot;
Me.DataGrid1.PageSize = psize
v_Phei := Pindex * Psize + Psize;

复制代码 代码如下:

Me.DataGrid1.AllowPaging = True

'------------------------------------------------------------------------------------
Oracle分页存储过程的思路于sqlserver的思路是一样的，但是我这里做了点改动，在因为Oracle的语法和规则的不同所以，Oracle分页存储过程看上去有点不一样。见笑，见笑！
Prcount out number --返回记录总数
'------------------------------------------------------------------------------------
------------------------------------------------------------显示任意页内容
TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
)
------------------------------------------------------------取分页总数
Dim psql As String = "select rownum rn,t.* from cd_ssxl t"
conn.Open()
----------------------------------------------------------------------------------------
dr.Close()
gridbind(e.NewPageIndex, psql, 20)
v_Plow := v_Phei - Psize + 1;
conn.Close()
as
v_count number;
Prcount out number
);
以下是在Oracle中实现的分页存储过程。
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;
End Sub

在Oracle的存储过程中返回记录集，需要用到游标变量，Oracle不能像sqlserver那样可以直接返回一个记录集。
PROCEDURE DotNetPagination(
begin
rotected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Me.DataGrid1.VirtualItemCount = cmd.Parameters("prcount").Value
cmd.Connection = conn
v_cur out type_cur --返回当前页数据记录
Me.DataGrid1.DataBind()
Psize in number, --页面大小
v_sql := 'select count(*) from (' || Psql || ')';
cmd.Parameters.Clear()

End DotNetPagination;
cmd.Parameters.Add("v_cur", Data.OracleClient.OracleType.Cursor).Direction = ParameterDirection.Output
Me.DataGrid1.CurrentPageIndex = e.NewPageIndex
gridbind(0, psql, 20)
v_prcount number;
v_Plow number;
If Not Page.IsPostBack Then

复制代码 代码如下:

Pcount out number, --返回分页总数
Psqlcount in varchar2, --产生dataset的sql语句
Dim psql As String = "select rownum rn,t.* from cd_ssxl t"
create or replace package body DotNet is
在.net调用返回记录集的存储过程，需要用到datareader，但是datareader不支持在datagrid中的分页，因此需要利用datagrid自定义分页功能。
end DotNot;
end DotNetPageRecordsCount;
AS
Prcount := v_prcount; --返回记录总数